/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.bancofloripa.dao;

import br.com.bancofloripa.entity.Funcionario;
import br.com.bancofloripa.exception.MensagemExceptionDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author fabianoc
 */
public class FuncionarioDAO extends BaseDao{

    public int insere(Funcionario cl) throws MensagemExceptionDAO {
        int id = 0;
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        try {
            StringBuilder sb = new StringBuilder();
            c = super.getConexaoBD();
            sb.append("INSERT INTO funcionario ");
            sb.append("(nome, cpf, senha, matricula) values (?, ?, ?, ?)");
            ps = c.prepareStatement(sb.toString(), ps.RETURN_GENERATED_KEYS);
            ps.setString(1, cl.getNome());
            ps.setString(2, cl.getCpf());
            ps.setString(3, cl.getSenha());
            ps.setString(4, cl.getMatricula());        
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            rs.next();
                System.out.println("id "+ rs.getInt(1));
                id = rs.getInt(1);
        } catch (SQLException ex) {
            throw new MensagemExceptionDAO("Erro de sql "+ex.getMessage());
            
        } catch (NullPointerException ex){
            throw new MensagemExceptionDAO("NullPointer "+ex.getMessage());
            
        } catch (MensagemExceptionDAO ex) {
            
            ex.getMessage();
        }        
        return id;
    }

    public void atualiza(Funcionario cl) throws MensagemExceptionDAO {
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        
        try {
            c = super.getConexaoBD();
            sb.append("UPDATE funcionario SET ");
            sb.append("nome = ? , cpf = ? , senha = ?, matricula = ? WHERE id = ?");
            ps = c.prepareStatement(sb.toString());
            ps.setString(1, cl.getNome());
            ps.setString(2, cl.getCpf());
            ps.setString(3, cl.getSenha());
            ps.setString(4, cl.getMatricula());        
            ps.setInt(5, cl.getId());        
            rs = ps.executeQuery();
        } catch (SQLException ex) {
            throw new MensagemExceptionDAO("Erro de sql "+ex.getMessage());
        } catch (MensagemExceptionDAO ex) {
            ex.getMessage();
        }        
    }

    public List<Funcionario> getAll() throws MensagemExceptionDAO {
       List<Funcionario> lista = new ArrayList<Funcionario>();
       PreparedStatement ps = null;
       Connection c = null ;
       ResultSet rs = null;
       try {
            c = super.getConexaoBD();
            ps = c.prepareStatement("select * from funcionario");
            rs = ps.executeQuery();
            while ( rs.next() == true ) {
                Funcionario cli = new Funcionario(rs.getString("matricula"), 
                        rs.getString("nome"), rs.getString("cpf"), 
                        rs.getString("senha"));
                lista.add( cli );
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
         finally {
            this.closeConnection(ps, c);
       }
       return lista;

    }

    public List<Object> getByFilter() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Funcionario> getByDesc(String nome) throws MensagemExceptionDAO {
        nome = "%"+ nome + "%";
        List<Funcionario> lista = new ArrayList<Funcionario>();
       PreparedStatement ps = null;
       Connection c = null ;
       ResultSet rs = null;
       try {
            c = super.getConexaoBD();
            ps = c.prepareStatement("select * from funcionario where nome like ? or cpf like ? or matricula like ?");
            ps.setString(1, nome);
            ps.setString(2, nome);
            ps.setString(3, nome);
            rs = ps.executeQuery();
            while ( rs.next() == true ) {
                Funcionario cli = new Funcionario(rs.getString("matricula"), 
                        rs.getString("nome"), rs.getString("cpf"), 
                        rs.getString("senha"));
                
                lista.add( cli );
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
         finally {
            this.closeConnection(ps, c);
       }
       return lista;
    }
    

    
    public static void main(String[] args) {
        try {
            
            FuncionarioDAO cd = new FuncionarioDAO();
            
            Funcionario cc = new Funcionario("a9h08h9", "jose", "75293754", "senha");
            System.out.println("ID: " + cd.insere(cc));
            System.out.println(cc);
            
            
            List<Funcionario> lista = new FuncionarioDAO().getAll();
            
            for ( int i = 0; i < lista.size(); i++ ){
                System.out.println(lista.get(i).getNome());
            }

            
        } catch (MensagemExceptionDAO ex) {
            ex.printStackTrace();
            //System.out.println(ex);
        }
    }
        
}
